﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using System.Data.OracleClient;

namespace MotivLogger
{
    public class OracleClientToolbox 
    {
       



        private OracleConnection Connection = null;
        private OracleTransaction transaction = null;
        private object lockobj = new object();
        private Dictionary<string, OracleCommand> Procedures = null;
        private static Dictionary<string, OracleClientToolbox> instances = null;
        public string name { get; set; }
        private static Random rnd = new Random(BitConverter.ToInt32(Guid.NewGuid().ToByteArray(), 0));
        //Logger l = new Logger();
        public OracleClientToolbox(string dbname, string user, string password) 
        {
            if (Connection == null)
            {
              
                string connectionString = String.Format("Data Source={2}; User ID={0}; Password={1}",user,password,dbname);
                Connection = new OracleConnection(connectionString);
                try
                {
                    Connection.Open();
                }
                catch {
                   // l.Error("Failed connection to Oracle", "OracleDbToolbox");
                }
                if (Connection.State == ConnectionState.Open)
                {
                    BeginTransaction();
                   // l.Info("Successfully connection to Oracle", "OracleDbToolbox");
                   
                }
            }
        }
        public static OracleClientToolbox Connect(string dbname, string user, string password)
        {
            
            string instance_name = String.Format("{0}:{1}@{2}{3}", user, password, dbname,rnd.Next());
            if (instances == null)
                instances = new Dictionary<string, OracleClientToolbox>();
            if (!instances.ContainsKey(instance_name))
            {
                instances.Add(instance_name, new OracleClientToolbox(dbname, user, password));
                instances[instance_name].name = instance_name;
            }
            return instances[instance_name];   
        }

        public void DisConnect()
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    CloseTransaction();
                    Connection.Close();
                    instances.Remove(name);
                }
            }
        }

        public void BeginTransaction() 
        {
            if (Connection != null)// && transaction == null)
            {
                lock (lockobj)
                {
                    transaction = Connection.BeginTransaction();
                }
            }
            
        }
        public void CloseTransaction()
        {
            if (Connection != null && transaction != null)
            {
                lock (lockobj)
                {
                    transaction.Dispose();
                }
            }
        }

        public void Commit()
        {
            if (Connection != null && transaction != null)
            {
                lock (lockobj)
                {
                    transaction.Commit();
                    BeginTransaction();
                }
            }
        }

        public void RollBack()
        {
            if (Connection != null && transaction != null)
            {
                lock (lockobj)
                {
                    transaction.Rollback();
                }
            }
        }

        public object Select(string what, string from, string where = null)
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    OracleCommand Command = new OracleCommand();
                    Command.Transaction = transaction;
                    Command.CommandText = String.Format("SELECT {0} FROM {1}", what, from);
                    if (where != null)
                        Command.CommandText += "WHERE " + where;
                    OracleDataReader DataReader = Command.ExecuteReader();
                    DataReader.Read();
                    Connection.Close();
                    return DataReader;
                }
            }
            return null;
        }

        public int Insert(string table, string values, string tablecolumns = null)
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    OracleCommand Command = new OracleCommand();
                    Command.Transaction = transaction;
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("INSERT {0}", table);
                    if (tablecolumns != null)
                        sb.AppendFormat(" ({0})", tablecolumns);
                    sb.AppendFormat(" VALUES ({0})", values);
                    Command.CommandText = sb.ToString();
                    var rows = Command.ExecuteNonQuery();
                    Connection.Close();
                    return rows;
                }
            }
            return -1;
        }

        public int Update(string what, string from, string where = null)
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    OracleCommand Command = new OracleCommand();
                    Command.Transaction = transaction;
                    Command.CommandText = String.Format("UPDATE {1} SET {0}", what, from);
                    if (where != null)
                        Command.CommandText += "WHERE " + where;
                    var rows = Command.ExecuteNonQuery();
                    Connection.Close();
                    return rows;
                }
            }
            return -1;
        }

        public object RunFunction(string FunctionName, params object[] args)
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    if (Procedures == null)
                        Procedures = new Dictionary<string, OracleCommand>();

                    if (!Procedures.ContainsKey(FunctionName))
                    {
                        var paramstring = (args.Length != 0) ? "?" : "";
                        for (int i = 1; i < args.Length-1; i++)
                            paramstring += ", ?";
                        //Procedures.Add(FunctionName, new OracleCommand("{call " + FunctionName + "(" + paramstring + ")}", Connection));
                        Procedures.Add(FunctionName, new OracleCommand( FunctionName , Connection));
                        Procedures[FunctionName].CommandType = CommandType.StoredProcedure;

                    }
                    var cmd = Procedures[FunctionName];
                    cmd.Parameters.Clear();
                    cmd.Transaction = transaction;
                    foreach (var arg in args)
                    {
                        OracleDbProcedureParam param = arg as OracleDbProcedureParam;
                        if (param.Name == "return")
                            cmd.Parameters.Add(param.Name, param.Type).Direction = ParameterDirection.ReturnValue;
                        else
                        {
                            if (param.Size == null)
                                cmd.Parameters.Add(param.Name, param.Type).Value = param.Value;
                            else
                                cmd.Parameters.Add(param.Name, param.Type, param.Size.Value).Value = param.Value;
                            cmd.Parameters[param.Name].Direction = ParameterDirection.Input;
                        }
                    }
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        //l.Error(e.Message, "OracleDbToolbox");
                    }
                    return (cmd.Parameters["return"]!=null)?cmd.Parameters["return"].Value:null;
                    
                }
            }
            return null;
        }

        public DataSet RunStoredProcedure(string ProcedureName, params object[] args)
        {
            if (Connection != null)
            {
                lock (lockobj)
                {
                    if (Procedures == null)
                        Procedures = new Dictionary<string, OracleCommand>();
                    if (!Procedures.ContainsKey(ProcedureName))
                    {
                        Procedures.Add(ProcedureName, new OracleCommand(ProcedureName, Connection));
                        Procedures[ProcedureName].CommandType = CommandType.StoredProcedure;
                    }
                    var cmd = Procedures[ProcedureName];
                    cmd.Parameters.Clear();
                    cmd.Transaction = transaction;
                    foreach (var arg in args)
                    {
                        OracleDbProcedureParam param = arg as OracleDbProcedureParam;
                        if (param.Size == null)
                            cmd.Parameters.Add(param.Name, param.Type).Value = param.Value;
                        else
                            cmd.Parameters.Add(param.Name, param.Type, param.Size.Value).Value = param.Value;
                        cmd.Parameters[param.Name].Direction = ParameterDirection.Input;
                    }

                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    //da.Fill(ds); 
              
                 
                    return ds;
                }
            }
            return null;
        }
    }
    public class OracleDbProcedureParam 
    {
        public string Name { get; private set; }
        public OracleType Type { get; private set; }
        public int? Size { get; private set; }
        public object Value { get; private set; }
        public OracleDbProcedureParam(string Name, OracleType Type, object Value, int? Size = null)
        {
            this.Name = Name;
            this.Type = Type;
            this.Value = Value;
            this.Size = Size;
        }
    }
    
}
